package stores;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import workspace.Workspace;
import beans.Operator;

public class OperatorStoreImpl implements OperatorStore {

	@Override
	public Operator loadOperatorByUsername(Workspace workspace,
			String felhasznaloNev, boolean isLogin) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Operator operator = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, FELHASZNALONEV, JELSZO, TELJESNEV, TELEFON, EMAIL FROM OPERATOR WHERE AKTIV = 1 AND FELHASZNALONEV = ?");
			int i = 1;
			ps.setString(i++, felhasznaloNev);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (isLogin) {
					operator = buildLoginOperator(rs);
				} else {
					operator = buildOperator(rs);
				}
			}
			return operator;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}

	}

	@Override
	public Operator loadOperatorById(Workspace workspace, long userId)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Operator operator = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, FELHASZNALONEV, JELSZO, TELJESNEV, TELEFON, EMAIL FROM OPERATOR WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				operator = buildOperator(rs);
			}
			return operator;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}

	}

	@Override
	public List<Operator> loadOperatorLista(Workspace workspace)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Operator> result = new ArrayList<Operator>();
		Operator operator = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, FELHASZNALONEV, JELSZO, TELJESNEV, TELEFON, EMAIL FROM OPERATOR WHERE AKTIV = 1");
			rs = ps.executeQuery();
			while (rs.next()) {
				operator = buildOperator(rs);
				result.add(operator);
			}
			return result;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}

	}

	@Override
	public Operator insertOperator(Workspace workspace, Operator newOperator)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("INSERT INTO OPERATOR(WRITER, FELHASZNALONEV, JELSZO, TELJESNEV, TELEFON, EMAIL, AKTIV) VALUES (?, ?, ?, ?, ?, ?, 1)");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, newOperator.getFelhasznaloNev());
			ps.setString(i++, newOperator.getJelszoHash());
			ps.setString(i++, newOperator.getTeljesNev());
			ps.setString(i++, newOperator.getTelefonSzam());
			ps.setString(i++, newOperator.getEmail());
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				newOperator.setId(getGeneratedKey(workspace));
				return newOperator;
			} else {
				return null;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public Operator updateOperator(Workspace workspace, Operator updatedOperator)
			throws SQLException {
		PreparedStatement ps = null;
		Operator operator = null;
		StringBuilder sql = new StringBuilder();

		workspace.startSession();

		sql.append("UPDATE OPERATOR SET WRITER = ?");
		if (updatedOperator.getJelszoHash() != null) {
			sql.append(", JELSZO = ?");
		}
		if (updatedOperator.getTeljesNev() != null) {
			sql.append(", TELJESNEV = ?");
		}
		if (updatedOperator.getTelefonSzam() != null) {
			sql.append(", TELEFON = ?");
		}
		if (updatedOperator.getEmail() != null) {
			sql.append(", EMAIL = ?");
		}
		sql.append(" WHERE ID = ?");

		try {
			ps = workspace.getPreparedStatement(sql.toString());
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			if (updatedOperator.getJelszoHash() != null) {
				ps.setString(i++, updatedOperator.getJelszoHash());
			}
			if (updatedOperator.getTeljesNev() != null) {
				ps.setString(i++, updatedOperator.getTeljesNev());
			}
			if (updatedOperator.getTelefonSzam() != null) {
				ps.setString(i++, updatedOperator.getTelefonSzam());
			}
			if (updatedOperator.getEmail() != null) {
				ps.setString(i++, updatedOperator.getEmail());
			}
			ps.setLong(i++, updatedOperator.getId());
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				operator = this.loadOperatorById(workspace,
						updatedOperator.getId());
			}
			return operator;

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteOperator(Workspace workspace, long userId)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("UPDATE OPERATOR SET WRITER = ?,  AKTIV = 0 WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setLong(i++, userId);
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	private Operator buildOperator(ResultSet rs) throws SQLException {
		int i = 1;
		Operator newOperator = new Operator();
		newOperator.setId(rs.getLong(i++));
		newOperator.setFelhasznaloNev(rs.getString(i++));
		i++;
		newOperator.setTeljesNev(rs.getString(i++));
		newOperator.setTelefonSzam(rs.getString(i++));
		newOperator.setEmail(rs.getString(i++));
		return newOperator;

	}

	private Operator buildLoginOperator(ResultSet rs) throws SQLException {
		int i = 1;
		Operator newOperator = new Operator();
		newOperator.setId(rs.getLong(i++));
		newOperator.setFelhasznaloNev(rs.getString(i++));
		newOperator.setJelszoHash(rs.getString(i++));
		newOperator.setTeljesNev(rs.getString(i++));
		newOperator.setTelefonSzam(rs.getString(i++));
		newOperator.setEmail(rs.getString(i++));
		return newOperator;

	}

	private long getGeneratedKey(Workspace workspace) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = workspace
					.getPreparedStatement("SELECT IDENT_CURRENT('OPERATOR')");
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			} else
				return -1L;
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

}
